import GRDB

// MARK: - 数据库管理器
final class DatabaseFolderManager {
    static let shared = DatabaseFolderManager()
    var dbQueue: DatabaseQueue
    
    private init() {
        do {
            let dbURL = try FileManager.default
                .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
                .appendingPathComponent("folder.db")
            
            dbQueue = try DatabaseQueue(path: dbURL.path)
            try migrateDatabase()
        } catch {
            fatalError("Database initialization failed: \(error)")
        }
    }
}

// MARK: - 版本管理
extension DatabaseFolderManager {
    /// 数据库版本枚举（集中管理）
    private enum Version: Int {
        case migration_1 = 1
        case migration_2 = 2
        case migration_3 = 3
        case migration_4 = 4
        case migration_5 = 5
        case migration_6 = 6
        case migration_7 = 7
    }
    
    /// 迁移操作结构体
    private struct Migration {
        let version: Version
        let migrate: (Database) throws -> Void
    }
    
    /// 所有迁移操作（按版本顺序排列）
    private var migrations: [Migration] {
        [
            Migration(version: .migration_1) { db in
                // 配置表
                if try! !db.tableExists("FolderConfig") {
                    try db.create(table: "FolderConfig") { t in
                        t.column("id", .integer).primaryKey()
                        t.column("doubleRow", .integer).notNull().defaults(to: false ? 1 : 0)
                        t.column("folderGridCount", .integer).notNull().defaults(to: 3)
                        t.column("sort", .integer).notNull().defaults(to: 0)
                        t.column("diarySort", .integer).notNull().defaults(to: 0)
                        // 由于 SQLite 没有直接存储数组的类型，这里用 text 存储 JSON 化的数组
                        t.column("aiRoleList", .text)
                        t.column("callTimeStart", .integer).notNull().defaults(to: 3600 * 9)
                        t.column("callTimeEnd", .integer).notNull().defaults(to: 3599 * 24)
                        t.column("dbFolderMainId", .text)
                        t.column("pictureFolderMainId", .text)
                        t.column("modifySign", .integer).notNull().defaults(to: false ? 1 : 0)
                    }
                }
                
                if try! !db.tableExists("folder") {
                    try db.create(table: "folder") { t in
                        t.column("objectId", .text).primaryKey()
                        t.column("userId", .text)
                        t.column("time", .integer).notNull().defaults(to: 0)
                        t.column("letterNum", .text)
                        t.column("doubleRow", .integer).notNull().defaults(to: true ? 1 : 0)
                        t.column("spanCount", .integer).notNull().defaults(to: 4)
                        t.column("sort", .integer).notNull().defaults(to: 0)
                        t.column("title", .text)
                        t.column("sortNum", .integer).notNull().defaults(to: 0)
                        t.column("parentId", .text)
                        t.column("name", .text).notNull().defaults(to: "")
                        t.column("count", .integer).notNull().defaults(to: 0)
                        t.column("coverFileId", .text)
                        t.column("coverTime", .integer).notNull().defaults(to: 0)
                        t.column("cover", .text).notNull().defaults(to: "")
                        t.column("iconType", .integer).notNull().defaults(to: 0)
                        t.column("isHideCover", .integer).notNull().defaults(to: false ? 1 : 0)
                        t.column("createTime", .double).notNull().defaults(to: 0)
                        t.column("updateTime", .double).notNull().defaults(to: 0)
                        t.column("modifySign", .integer).notNull().defaults(to: false ? 1 : 0)
                        t.column("status", .integer).notNull().defaults(to: IStatusType.NORMAL.rawValue)
                    }
                }
                // 创建picture表（幂等操作）
                if try! !db.tableExists("picture") {
                    try db.create(table: "picture") { t in
                        t.column("objectId", .text).primaryKey()
                        t.column("diaryId", .text)
                        t.column("fileId", .text)
                        t.column("folderId", .text)
                        t.column("userId", .text)
                        t.column("thumbnail", .text)
                        t.column("name", .text)
                        t.column("letterNum", .text)
                        t.column("url", .text)
                        t.column("duration", .integer).notNull().defaults(to: 0)
                        t.column("width", .integer).notNull().defaults(to: 0)
                        t.column("height", .integer).notNull().defaults(to: 0)
                        t.column("time", .integer).notNull().defaults(to: 0)
                        t.column("sort", .integer).notNull().defaults(to: 0)
                        t.column("size", .integer).notNull().defaults(to: 0)
                        t.column("hash", .text)
                        t.column("mimeType", .text)
                        t.column("createTime", .integer).notNull().defaults(to: 0)
                        t.column("updateTime", .integer).notNull().defaults(to: 0)
                        t.column("modifySign", .integer).notNull().defaults(to: false ? 1 : 0)
                        t.column("status", .integer).notNull().defaults(to: IStatusType.NORMAL.rawValue)
                        t.column("urlTime", .integer).notNull().defaults(to: 0)
                    }
                }
                // 角色
                if try! !db.tableExists("AiRole") {
                    try db.create(table: "AiRole") { t in
                        t.column("objectId", .text).primaryKey()
                        t.column("userId", .text)
                        t.column("name", .text)
                        t.column("name2", .text)
                        t.column("describe", .text)
                        t.column("describe2", .text)
                        t.column("hideDescribe", .text)
                        t.column("avatar", .text)
                        t.column("sexName", .text)
                        // 假设 AiVoiceBean 是自定义类型，用 text 存储相关信息，可按需调整
                        t.column("voice", .text)
                        t.column("isSystem", .integer).notNull().defaults(to: false ? 1 : 0)
                        t.column("createTime", .integer).notNull().defaults(to: 0)
                        t.column("updateTime", .integer).notNull().defaults(to: 0)
                        t.column("modifySign", .integer).notNull().defaults(to: false ? 1 : 0)
                        t.column("status", .integer).notNull().defaults(to: IStatusType.NORMAL.rawValue)
                        t.column("sort", .integer).notNull().defaults(to: 0)
                    }
                }
                //标签
                if try! !db.tableExists("tag") {
                    try db.create(table: "tag") { t in
                        t.column("objectId", .text).primaryKey()
                        t.column("text", .text)
                        t.column("userId", .text)
                        t.column("sort", .double).notNull().defaults(to: 0)
                        t.column("color", .text)
                        t.column("createTime", .double).notNull().defaults(to: 0)
                        t.column("updateTime", .double).notNull().defaults(to: 0)
                        t.column("modifySign", .integer).notNull().defaults(to: false ? 1 : 0)
                        t.column("status", .integer).notNull().defaults(to: IStatusType.NORMAL.rawValue)
                    }
                }
            },
            Migration(version: .migration_2) { db in
                if try! !db.columnExists("chatBg", in: "AiRole") {
                    try db.execute(sql: "ALTER TABLE AiRole ADD COLUMN chatBg TEXT")
                }
                
                if try! !db.columnExists("diaryBg", in: "AiRole") {
                    try db.execute(sql: "ALTER TABLE AiRole ADD COLUMN diaryBg TEXT")
                }
                if try! !db.columnExists("bubbleId", in: "AiRole") {
                    try db.execute(sql: "ALTER TABLE AiRole ADD COLUMN bubbleId TEXT")
                }
            },
            Migration(version: .migration_3) { db in
                if try! !db.columnExists("bubbleCommentId", in: "AiRole") {
                    try db.execute(sql: "ALTER TABLE AiRole ADD COLUMN bubbleCommentId TEXT")
                }
            },
            
            Migration(version: .migration_4) { db in
                if try! !db.columnExists("aiRoleOften", in: "FolderConfig") {
                    try db.execute(sql: "ALTER TABLE FolderConfig ADD COLUMN aiRoleOften TEXT")
                }
            },
            Migration(version: .migration_5) { db in
                if try! !db.columnExists("hideChatRecord", in: "AiRole") {
                    try db.execute(sql: "ALTER TABLE AiRole ADD COLUMN hideChatRecord INTEGER DEFAULT 0")
                }
            },
            Migration(version: .migration_6) { db in
                if try! !db.columnExists("describe", in: "picture") {
                    try db.execute(sql: "ALTER TABLE picture ADD COLUMN describe TEXT")
                    try db.execute(sql: "ALTER TABLE picture ADD COLUMN oldPath TEXT")
                }
            },
            Migration(version: .migration_7) { db in
                if try! !db.columnExists("describeChat", in: "AiRole") {
                    try db.execute(sql: "ALTER TABLE AiRole ADD COLUMN describeChat TEXT")
                }
            },
        ].sorted { $0.version.rawValue < $1.version.rawValue } // 确保升序排列
    }
    
    /// 执行数据库迁移
    private func migrateDatabase() throws {
        let currentVersion = try getCurrentVersion()
        showLog("Current database version: \(currentVersion)")
        
        // 筛选需要执行的迁移
        let pendingMigrations = migrations.filter {
            $0.version.rawValue > currentVersion
        }
        
        guard !pendingMigrations.isEmpty else {
            showLog("Database is up to date")
            return
        }
        
        showLog("Pending migrations: \(pendingMigrations.count)")
        
        // 执行迁移队列
        for migration in pendingMigrations {
            try dbQueue.write { db in
                do {
                    showLog("Migrating to version \(migration.version.rawValue)...")
                    try migration.migrate(db)
                    try setVersion(db: db, version: migration.version.rawValue)
                    showLog("Migration to version \(migration.version.rawValue) succeeded")
                } catch {
                    showLog("Migration to version \(migration.version.rawValue) failed: \(error)")
                    throw error
                }
            }
        }
        
        showLog("Final database version: \(try getCurrentVersion())")
    }
}

// MARK: - 版本控制
extension DatabaseFolderManager {
    /// 获取当前数据库版本
    private func getCurrentVersion() throws -> Int {
        try dbQueue.read { db in
            try Int.fetchOne(db, sql: "PRAGMA user_version") ?? 0
        }
    }
    
    /// 设置数据库版本
    private func setVersion(db: Database, version: Int) throws {
        try db.execute(sql: "PRAGMA user_version = \(version)")
    }
}

// MARK: - 使用示例
/*
 // 在AppDelegate中初始化
 _ = DatabaseFolderManager.shared
 
 // 检查当前版本
 do {
 let version = try DatabaseManager.shared.getCurrentVersion()
 showLog("Current DB version: \(version)")
 } catch {
 showLog("Version check failed: \(error)")
 }
 */
